<?php

namespace app\api\service\admin;

use app\common\library\Auth;
use think\Validate;
use think\Db;

use fast\Random;
use Exception;
use think\exception\PDOException;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class InformalTalkService
{
    // 列表
    public static function index($data)
    {
        $auth = Auth::instance();
        $limit = $data['limit'] ?? 10;
        $full_name = $data['full_name'] ?? '';
        $english_name = $data['english_name'] ?? '';
        $id = $data['id'] ?? '';
        $studio_id = $data['studio_id'] ?? '';
        $channel = $data['channel'] ?? '';
        $level = $data['level'] ?? '';
        $age = $data['age'] ?? '';
        $gender = $data['gender'] ?? '';
        $contact = $data['contact'] ?? '';
        $start_date = $data['start_date'] ?? '';
        $end_date = $data['end_date'] ?? '';

        $where = [];
        if ($full_name) $where['uc.surname|uc.given_name'] = ['like', '%' . addslashes($full_name) . '%'];
        if ($english_name) $where['uc.english_name'] = ['like', '%' . addslashes($english_name) . '%'];
        if ($id) $where['ua.id'] = intval($id);
        if ($studio_id) $where['uc.studio_id'] = ['like', '%' . addslashes($studio_id) . '%'];
        if ($channel) $where['uc.channel'] = addslashes($channel);
        if ($level) $where['uc.level'] = addslashes($level);
        if ($age) $where['uc.birthday'] = ['like', '%' . (date('Y') - intval($age)) . '%'];
        if ($gender) $where['uc.gender'] = addslashes($gender);
        if ($contact) $where['uc.contact'] = ['like', '%' . addslashes($contact) . '%'];

        if ($start_date && strtotime($start_date)) {
            $where['uc.createtime'] = ['>=', strtotime($start_date)];
        }
        if ($end_date && strtotime($end_date)) {
            $where['uc.createtime'] = isset($where['uc.createtime'])
                ? array_merge($where['uc.createtime'], ['<=', strtotime($end_date)])
                : ['<=', strtotime($end_date)];
        }

        // 预加载关联数据
        $studio = \app\admin\model\CustomersAppointment::alias('ua')
            ->join('__USER_CUSTOMERS__ uc', 'uc.id = ua.customers_id')
            ->with(['teacher' => function($query) {
                $query->field('id,nickname');
            }, 'teacherRole' => function($query) {
                $query->field('id,name');
            }, 'appointedBy' => function($query) {
                $query->field('id,nickname');
            }])
            ->where($where)
            ->field('ua.id,uc.surname,uc.given_name,uc.english_name,uc.birthday,uc.gender,uc.primary_phone,ua.createtime,ua.teacher,ua.teacher_role,ua.appointed_by,ua.appointed_on,ua.paid')
            ->order('uc.id', 'desc')
            ->paginate($limit)
            ->each(function($item, $key) {
                $item['full_name'] = $item['given_name'] . ' ' . $item['surname'];
                $item['birthday'] = substr($item['birthday'], 0, 4);
                $item['age'] = (int)date('Y') - $item['birthday'];
                $item['teacher'] = $item['teacher']['nickname'] ?? '';
                $item['teacher_role'] = $item['teacher_role']['name'] ?? '';
                $item['appointed_by'] = $item['appointed_by']['nickname'] ?? '';
                return $item;
            });

        return ['code' => 200, 'msg' => 'Successful', 'state' => 'OK', 'data' => $studio];
    }


    // 添加
    public static function add($data)
    {
        // 验证规则
        $validate = new Validate([
            'customers_id' => 'require',
            'type' => 'require',
            'date' => 'require',
            'collected_by' => 'require',
            'followup_by' => 'require',
            'description' => 'require',
        ], [
            'customers_id.require' => 'CustomersID is not empty',
            'type.require' => 'Type is not empty',
            'date.require' => 'Date is not empty',
            'collected_by.require' => 'Collected By is not empty',
            'followup_by.require' => 'Followup By is not empty',
            'description.require' => 'Description is not empty',
        ]);

        if (!$validate->check($data)) {
            return ['code'=>400, 'msg'=>$validate->getError(), 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        // 启用事务将数据保存到数据库
        try {
            Db::startTrans();

            $result = \app\admin\model\CustomersInformalTalk::create([
                'customers_id' => $data['customers_id'],
                'type' => $data['type'],
                'date' => $data['date'],
                'description' => $data['description'],
                'collected_id' => $data['collected_by'],
                'followup_id' => $data['followup_by']
            ]);

            if($result){
                Db::commit();
                return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$result];
            }else{
                Db::rollback();
                return ['code'=>400, 'msg'=>'Failed', 'state'=>'FAILED', 'data'=>null];
            }
        }
        catch (\Exception $e) {
            Db::rollback();
            return ['code' => 400, 'msg' => 'Database error' , 'state' => 'DATABASE_ERROR', 'data' => null];
        }
    }


    // 编辑
    public static function edit($data)
    {
        $data['mobile'] = $data['phone'];
        $data['primary_relationship'] = isset($data['primary_relationship']) ? $data['primary_relationship'] : '';
        $data['primary_name'] = isset($data['primary_name']) ? $data['primary_name'] : '';
        $data['primary_phone'] = isset($data['primary_phone']) ? $data['primary_phone'] : '';
        $data['primary_email'] = isset($data['primary_email']) ? $data['primary_email'] : '';
        $data['primary_account'] = isset($data['primary_account']) ? $data['primary_account'] : '';
        $data['secondary_relationship'] = isset($data['secondary_relationship']) ? $data['secondary_relationship'] : '';
        $data['secondary_name'] = isset($data['secondary_name']) ? $data['secondary_name'] : '';
        $data['secondary_phone'] = isset($data['secondary_phone']) ? $data['secondary_phone'] : '';
        $data['secondary_email'] = isset($data['secondary_email']) ? $data['secondary_email'] : '';
        $data['secondary_account'] = isset($data['secondary_account']) ? $data['secondary_account'] : '';

        if (empty($data['id']) || !is_numeric($data['id'])) {
            return ['code'=>400, 'msg'=>'Invalid parameters', 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        $customers_id = \app\admin\model\CustomersAppointment::where('id', $data['id'])->value('customers_id');
        if (!$customers_id) {
            return ['code'=>400, 'msg'=>'Information does not exist', 'state'=>'NOT_FOUND', 'data'=>null];
        }
        $data['id'] = $customers_id;

        unset($data['phone']);
        $auth = Auth::instance();
        // 验证规则
        $validate = new Validate([
            'studio_id' => 'require',
            'surname' => 'require',
            'given_name' => 'require',
            'english_name' => 'require',
            'status'    =>  'require',
            'gender' => 'require',
            'birthday' => 'require',
            'mobile' => 'require',
            'channel' => 'require',
            'school' => 'require',
            'school_grade' => 'require',
            'description' => 'require',
            'avatar' => 'require',
            'primary_relationship' => 'require',
            'primary_name' => 'require',
            'primary_phone' => 'require',
            'primary_account' => 'require',
//            'rules' => 'require',
        ], [
            'studio_id.require' => 'StudioID is not empty',
            'surname.require' => 'Surname is not empty',
            'given_name.require' => 'Given name is not empty',
            'english_name.require' => 'English name is not empty',
            'status.require' => 'Status is not empty',
            'gender.require' => 'Gender is not empty',
            'birthday.require' => 'Birthday is not empty',
            'mobile.require' => 'Mobile is not empty',
            'channel.require' => 'Channel is not empty',
            'school.require' => 'School is not empty',
            'school_grade.require' => 'School Grade is not empty',
            'description.require' => 'Description not empty',
            'avatar.require' => 'Avatar is not empty',
            'primary_relationship.require' => 'Primary Relationship is not empty',
            'primary_name.require' => 'Primary Name is not empty',
            'primary_phone.require' => 'Primary Phone is already exists',
            'primary_account.require' => 'Primary Social Acount is not empty',

        ]);

        if (!$validate->check($data)) {
            return ['code'=>400, 'msg'=>$validate->getError(), 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        // 启用事务将数据保存到数据库
        try {

            $result = \app\admin\model\UserCustomers::update($data);

            if($result){
                Db::commit();
                return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$result];
            }else{
                Db::rollback();
                return ['code'=>400, 'msg'=>'Failed', 'state'=>'FAILED', 'data'=>null];
            }
        }
        catch (\Exception $e) {
            Db::rollback();
            return ['code' => 400, 'msg' => 'Database error: ' . $e->getMessage(), 'state' => 'DATABASE_ERROR', 'data' => null];
        }
    }

    // 删除
    public static function del($data)
    {
        $auth = Auth::instance();
        // 验证$data['id']是否为空且为数字

        if (empty($data['id']) || !is_numeric($data['id'])) {
            return ['code'=>400, 'msg'=>'Invalid parameters', 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        // 查询学习馆数据
        $details = \app\admin\model\CustomersAppointment::find($data['id']);
        if (!$details) {
            return ['code'=>400, 'msg'=>'Customer does not exist', 'state'=>'NOT_FOUND', 'data'=>null];
        }

        // 启用事务删除数据
        try {
            $result = \app\admin\model\CustomersAppointment::destroy($data['id']);

            if($result){
                Db::commit();
                return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$result];
            }else{
                Db::rollback();
                return ['code'=>400, 'msg'=>'Failed', 'state'=>'FAILED', 'data'=>null];
            }
        }
        catch (\Exception $e) {
            Db::rollback();
            return ['code' => 400, 'msg' => 'Database error: ' . $e->getMessage(), 'state' => 'DATABASE_ERROR', 'data' => null];
        }
    }

    // 支付
    public static function payment($data)
    {
        $data['paidtime'] = $data['payment_date'];
        $data['payment_by'] = $data['payment_collected_by'];
        unset($data['payment_date']);
        unset($data['payment_collected_by']);
        $auth = Auth::instance();
        // 验证规则
        $validate = new Validate([
            'paid' => 'require',
            'paidtime' => 'require',
            'payment_by' => 'require',
        ], [
            'paid.require' => 'Paid is not empty',
            'paidtime.require' => 'Payment Date is not empty',
            'payment_by.require' => 'Payment Collected By is not empty',

        ]);

        if (!$validate->check($data)) {
            return ['code'=>400, 'msg'=>$validate->getError(), 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        // 启用事务将数据保存到数据库
        try {

            $data['order_no'] = getNewOrderNo(16);

//            return $data;
            $result = \app\admin\model\CustomersOrder::update($data);

            if($result){
                Db::commit();
                $statusInfo = \app\admin\model\CustomersAppointment::where('id', $data['id'])->field('customers_id,type,number,order_no, amount,paid, paidtime, payment_by')->find();
                $statusInfo['nickname'] = \app\admin\model\UserCustomers::where('id', $statusInfo['customers_id'])->value('english_name');

                return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$statusInfo];
            }else{
                Db::rollback();
                return ['code'=>400, 'msg'=>'Failed', 'state'=>'FAILED', 'data'=>null];
            }
        }
        catch (\Exception $e) {
            Db::rollback();
            return $e->getMessage();
            return ['code'=>400, 'msg'=>'Database error', 'state'=>'DATABASE_ERROR', 'data'=>null];
        }
    }

    // 记录
    public static function record($data)
{
    try {
        $auth = Auth::instance();
        $limit = isset($data['limit']) ? (int)$data['limit'] : 10;

        $validate = new Validate([
            'customers_id' => 'require|number',
        ], [
            'customers_id.require' => 'CustomersID is not empty',
            'customers_id.number' => 'CustomersID must be a number',
        ]);

        if (!$validate->check($data)) {
            return ['code'=>400, 'msg'=>$validate->getError(), 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        $customersId = (int)$data['customers_id'];

        // 预加载关联数据
        $list = \app\admin\model\CustomersInformalTalk::with([
            'collector:collected_id,nickname',
            'customer:customers_id,avatar,english_name'
        ])
        ->where('customers_id', $customersId)
        ->paginate($limit)
        ->each(function ($item, $key) {
            $item['collected_by'] = $item->collector->nickname ?? '';
            $item['customers_name'] = $item->customer->english_name ?? '';
            $item['customers_avatar'] = $item->customer->avatar ?? '';
            unset($item->collector, $item->customer);
        });

        return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$list];
    } catch (\Exception $e) {
        return $e->getMessage();
        return ['code'=>500, 'msg'=>'Server Error', 'state'=>'INTERNAL_ERROR', 'data'=>null];
    }
}


    // 详情
    public static function view($data)
    {
        $auth = Auth::instance();
        if (empty($data['id']) || !is_numeric($data['id'])) {
            return ['code'=>400, 'msg'=>'Invalid parameters', 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        $customers_id = \app\admin\model\CustomersAppointment::where('id', $data['id'])->value('customers_id');
        if (!$customers_id) {
            return ['code'=>400, 'msg'=>'Information does not exist', 'state'=>'NOT_FOUND', 'data'=>null];
        }

        $result = \app\admin\model\UserCustomers::where(['id'=>$customers_id])->field('avatar,studio_id,surname,given_name,status,birthday,gender,mobile,channel,school,school_grade,description,primary_relationship,primary_name,primary_phone,primary_email,primary_account,secondary_relationship,secondary_name,secondary_phone,secondary_email,secondary_account,collected_id,createtime,assign_to,assign_id,assigntime')->find();
//        RETURN $result;
        if($result){
            $result['age'] = date('Y') - substr($result['birthday'], 0, 4);
            $result['avatar'] = $result['avatar'] ? $result['avatar'] : '/assets/img/avatar.png';
            $collected = \app\admin\model\User::where('id', $result['collected_id'])->field('id,surname,given_name,group_id')->find();
            $result['collected_by'] = $collected ? $collected['given_name'] . $collected['surname'] : null;
            $collected_role = \app\admin\model\UserGroup::where('id', $collected['group_id'])->value('name');
            $result['collected_role'] = $collected_role ? $collected_role : null;
            $result['collected_date'] = date('Y-m-d H:i:s', $result['createtime']);

            $assign = \app\admin\model\User::where('id', $result['assign_id'])->field('id,surname,given_name,group_id')->find();
            $result['assigned_by'] = $assign ? $assign['given_name'] . $assign['surname'] : null;
            $assign_role = \app\admin\model\UserGroup::where('id', $assign['group_id'])->value('name');
            $result['assigned_role'] = $assign_role ? $assign_role : null;
            $result['assigned_date'] = date('Y-m-d H:i:s', $result['assigntime']);
        }
        return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$result];
    }

}
